<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

function create_table($name) {
    switch ($name) {
        case "producer":
            create_producer_table();
            break;
        case "film":
            create_film_table();
            break;
        case "agent":
            create_agent_table();
            break;
        case "ticket":
            create_ticket_table();
            break;
        case "customer":
            create_customer_table();
            break;
        case "kteam":
            create_kteam_table();
            break;
        case "centre":
            create_centre_table();
            break;
        case "level":
            create_level_table();
            break;
    }
}

function create_producer_table() {
  return "CREATE TABLE `kteam`.`producer` (
          `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          `name` TEXT  NOT NULL,
          `code` VARCHAR(20)  NOT NULL,
          `phone` INTEGER  NOT NULL,
          `address` LONGTEXT  NOT NULL,
          `banner_name` TEXT  NOT NULL,
          `password` TEXT NOT NULL,
          `remarks` LONGTEXT ,
          PRIMARY KEY (`id`),
          UNIQUE (`code`)
          )";
}

function create_film_table() {
    return "CREATE TABLE `kteam`.`film` (
            `id` BIGINT  NOT NULL AUTO_INCREMENT,
            `name` TEXT  NOT NULL,
            `code` VARCHAR(20)  NOT NULL,
            `total_no_tickets` BIGINT  NOT NULL,
            `sold_no_tickets` BIGINT  NOT NULL,
            `producer_id` BIGINT  NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE (`code`)
            )";
}

function create_centre_table() {
    return "CREATE TABLE `kteam`.`centre` (
            `id` INT  NOT NULL AUTO_INCREMENT,
            `name` TEXT  NOT NULL,
            `code` VARCHAR(20)  NOT NULL,
            `aplh_code` TEXT  NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE (`code`)
            )";
}

function create_agent_table() {
    return "CREATE TABLE `kteam`.`agent` (
            `id` BIGINT  NOT NULL AUTO_INCREMENT,
            `name` TEXT  NOT NULL,
            `code` VARCHAR(20)  NOT NULL,
            `centre_code` VARCHAR(20)  NOT NULL,
            `mobile_phone` TEXT  NOT NULL,
            `office_phone` TEXT  NOT NULL,
            `address` LONGTEXT  NOT NULL,
            `password` TEXT NOT NULL,
            PRIMARY KEY (`id`),
            FOREIGN KEY (`centre_code`) REFERENCES centre(`code`),
            UNIQUE (`code`)
            )";
}

function create_ticket_table() {
    return "CREATE TABLE `kteam`.`ticket` (
            `id` BIGINT(20)  NOT NULL AUTO_INCREMENT,
            `ticket_no` BIGINT(20)  NOT NULL,
            `max_coupon_value` INT  NOT NULL,
            `value` INT  NOT NULL,
            `parent_ticket_no` BIGINT(20)  NOT NULL,
            `level` INT  NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE(`ticket_no`)
          )";
}

function create_customer_table() {
    return "CREATE TABLE `kteam`.`customer` (
            `id` BIGINT(20)  NOT NULL AUTO_INCREMENT,
            `name` TEXT  NOT NULL,
            `centre_code` VARCHAR(20)  NOT NULL,
            `agent_code` VARCHAR(20)  NOT NULL,
            `film_code` VARCHAR(20)  NOT NULL,
            `ticket_no` VARCHAR(20)  NOT NULL,
            `phone` BIGINT(20)  NOT NULL,
            `date_issue` DATE  NOT NULL,
            `time_issue` TIME  NOT NULL,
            `ticket_rate` INT  NOT NULL,
            `coupon_value` INT  NOT NULL,
            PRIMARY KEY(`id`),
            UNIQUE(`ticket_no`)
            )";
}

function create_kteam_table() {
    return "CREATE TABLE `kteam`.`kteam` (
            `id` INT  NOT NULL AUTO_INCREMENT,
            `username` TEXT  NOT NULL,
            `password` TEXT  NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE (`username`)
            )";
}

function create_level_table() {
   return "CREATE TABLE `kteam`.`level` (
            `level` INT  NOT NULL,
            `count` INT  NOT NULL
            )";
}

function create_ticketinfo_table() {
    return "CREATE TABLE  `kteam`.`ticketinfo` (
            `id` bigint(20) NOT NULL AUTO_INCREMENT,
            `ticket_rate` int(11) NOT NULL,
            `coupon_value` int(11) NOT NULL,
            `l0` float NOT NULL,
            `l1` float NOT NULL,
            `l2` float NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `ticket_rate` (`ticket_rate`)
          )";
}

function delete_table($name) {
    return "DROP TABLE " . $name;
}

function insert_producer($fields) {
    return "INSERT INTO producer (name, code, phone, address,
                                banner_name, password, remarks)
            VALUES ('$fields[0]', '$fields[1]', '$fields[2]', '$fields[3]',
                    '$fields[4]', '$fields[5]', '$fields[6] ' )  ";
}

function update_producer($id, $fields) {
    return "UPDATE producer
            SET name='$fields[0]', code='$fields[1]',
                phone='$fields[2]', address='$fields[3]',
                banner_name='$fields[4]', password='$fields[5]',
                remarks='$fields[6]'
            WHERE id='$id' ";
}

function get_producer_list() {
    return "SELECT name, code from producer";
}

function get_producer_id_from_code($producer_code) {
    return "SELECT id from producer WHERE code='".$producer_code."'";
}

function get_producer_code_from_id($id) {
    return "SELECT code from producer where id='".$id."'";
}

function get_producer($producer_code) {
    return "SELECT * from producer WHERE code='".$producer_code."'";
}

function get_all_producers() {
    return "SELECT * from producer";
}

function delete_producer($producer_code) {
    return "DELETE from producer WHERE code='".$producer_code."'";
}

function insert_film($fields) {
    return "INSERT INTO film (name, code, total_no_tickets, sold_no_tickets, producer_id)
            VALUES ('$fields[0]', '$fields[1]', '$fields[2]', '$fields[3]', '$fields[4]')  ";
}

function update_film($id, $fields) {
    return "UPDATE film 
            SET name='$fields[0]', code='$fields[1]', total_no_tickets='$fields[2]',
                sold_no_tickets='$fields[3]', producer_id='$fields[4]'
            WHERE id='$id' ";
}

function update_film_sold_tickets($id, $sold) {
    return "UPDATE film
            SET sold_no_tickets='$sold'
            WHERE id='$id' ";
}

function get_film_list() {
    return "SELECT name, code from film";
}

function get_film($film_code) {
    return "SELECT * from film WHERE code='".$film_code."'";
}

function get_all_films() {
    return "SELECT * from film";
}

function delete_film($film_code) {
    return "DELETE from film WHERE code='".$film_code."'";
}

function insert_centre($fields) {
    return "INSERT INTO centre (name, code, aplh_code)
            VALUES ('$fields[0]', '$fields[1]', '$fields[2]' )  ";
}

function update_centre($id, $fields) {
    return "UPDATE centre
            SET name='$fields[0]', code='$fields[1]', aplh_code='$fields[2]'
            WHERE id='$id' ";
}

function get_centre_list() {
    return "SELECT name, code from centre ORDER BY CONVERT (code , UNSIGNED INTEGER)";
}

function get_centre($centre_code) {
    return "SELECT * from centre WHERE code='".$centre_code."'";
}

function get_all_centres() {
    return "SELECT * from centre ORDER BY CONVERT (code , UNSIGNED INTEGER)";
}

function delete_centre($centre_code) {
    return "DELETE from centre WHERE code='".$centre_code."'";
}

function insert_agent($fields) {
    return "INSERT INTO agent (name, code, centre_code, mobile_phone, office_phone, address, password)
            VALUES ('$fields[0]', '$fields[1]', '$fields[2]', '$fields[3]', 
                    '$fields[4]', '$fields[5]', '$fields[6]')  ";
}

function update_agent($id, $fields) {
    return "UPDATE agent
            SET name='$fields[0]', code='$fields[1]', centre_code='$fields[2]',
                mobile_phone='$fields[3]', office_phone='$fields[4]',
                address='$fields[5]', password='$fields[6]'
            WHERE id='$id' ";
}

function get_agent_list($centre_code) {
    if(is_null($centre_code)) {
        return "SELECT name, code from agent";
    } else {
        return "SELECT name, code from agent where centre_code='".$centre_code."'";
    }
}

function get_agent($code) {
    return "SELECT * from agent WHERE code='".$code."'";
}

function get_all_agents() {
    return "SELECT * from agent";
}

function delete_agent($code) {
    return "DELETE from agent WHERE code='".$code."'";
}

function insert_customer($fields) {
    return "INSERT INTO customer (name, centre_code, agent_code, film_code, phone,
            ticket_no, date_issue, time_issue, ticket_rate, coupon_value)
            VALUES ('$fields[0]', '$fields[1]', '$fields[2]', '$fields[3]', $fields[4],
                    '$fields[5]', '$fields[6]', '$fields[7]', '$fields[8]', '$fields[9]')  ";
}

function update_customer($id, $fields) {
    return "UPDATE customer
            SET name='$fields[0]', centre_code='$fields[1]', agent_code='$fields[2]',
                film_code='$fields[3]', phone='$fields[4]', ticket_no='$fields[5]'
            WHERE id='$id' ";
}

function get_customer_list() {
    return "SELECT name, ticket_no from customer";
}

function get_customer($ticket_no) {
    return "SELECT * from customer WHERE ticket_no='".$ticket_no."'";
}

function get_all_customers() {
    return "SELECT * from customer";
}

function get_all_customers_agent($agent_code) {
    return "SELECT * from customer WHERE agent_code='".$agent_code."'";
}

function get_all_customers_film($film_code) {
    return "SELECT * from customer WHERE film_code='".$film_code."'";
}

function get_all_customers_film_rate($film_code, $ticket_rate) {
    return "SELECT * from customer WHERE film_code='".$film_code."' and ticket_rate='".$ticket_rate."'";
}

function delete_customer($code) {
    return "DELETE from customer WHERE ticket_no='".$ticket_no."'";
}

function insert_ticketinfo($fields) {
    return "INSERT INTO ticketinfo (ticket_rate, coupon_value,
                                l0, l1, l2)
            VALUES ('$fields[0]', '$fields[1]',
                    '$fields[2]', '$fields[3]', '$fields[4]' )  ";
}

function update_ticketinfo($id, $fields) {
    return "UPDATE ticketinfo
            SET ticket_rate='$fields[0]', coupon_value='$fields[1]',
                l0='$fields[2]', l1='$fields[3]',
                l2='$fields[4]'
            WHERE id='$id' ";
}

function get_ticketinfo_list() {
    return "SELECT coupon_value, ticket_rate from ticketinfo ORDER BY CONVERT (ticket_rate , UNSIGNED INTEGER)";
}

function get_ticketinfo($ticket_rate) {
    return "SELECT * from ticketinfo WHERE ticket_rate='".$ticket_rate."'";
}

function get_all_ticketinfos() {
    return "SELECT * from ticketinfo ORDER BY CONVERT (ticket_rate , UNSIGNED INTEGER)";
}

function delete_ticketinfo($ticket_rate) {
    return "DELETE from ticketinfo WHERE ticket_rate='".$ticket_rate."'";
}

function insert_kteam_user($fields) {
    return "INSERT into kteam (username, password) VALUES ('$fields[0]', '$fields[1]')";
}

function login_user($db_table, $username, $password) {
    switch($db_table) {
        case 'kteam':
            $field = 'username';
            break;
        default:
            $field = 'code';
            break;
    }
    return "SELECT * from " . $db_table . " where " . $field . " = '" . $username . "' AND password = '". $password . "'";
}

function get_level_info() {
    return "SELECT * from level";
}

function get_last_ticket() {
    return "SELECT * from ticket ORDER BY id DESC LIMIT 1";
}

function get_all_tickets_level($level) {
    return "SELECT * from ticket WHERE level='".$level."' ORDER BY id";
}

function get_first_ticket_level($level) {
    return "SELECT * from ticket WHERE level='".$level."' ORDER BY id ASC LIMIT 1";
}

function get_all_childs($parent_ticket_no) {
    return "SELECT * from ticket where parent_ticket_no='".$parent_ticket_no."'";
}

function insert_ticket($fields) {
    return "INSERT into ticket (ticket_no, max_coupon_value, value, parent_ticket_no, level)
        VALUES ('$fields[0]', '$fields[1]', '$fields[2]', '$fields[3]', '$fields[4]')";
}

function update_ticket_value($ticket_no, $value) {
    return "UPDATE ticket
        SET value='$value' WHERE ticket_no='$ticket_no'";
}

function update_ticket_level($ticket_no, $level) {
    return "UPDATE ticket
        SET level='$level' WHERE ticket_no='$ticket_no'";
}

function update_ticket_level_and_value($ticket_no, $level, $value) {
    return "UPDATE ticket
            SET level='$level', value='$value'
            WHERE ticket_no='$ticket_no'";
}

function update_ticket_level_and_parent($ticket_no, $level, $parent_ticket_no) {
    return "UPDATE ticket
            SET level='$level', parent_ticket_no='$parent_ticket_no'
            WHERE ticket_no='$ticket_no'";
}

function get_ticket($ticket_no) {
    return "SELECT * from ticket WHERE ticket_no='$ticket_no'";
}

function update_sem($id, $value) {
    return "UPDATE sem
            SET value='$value' WHERE id='$id'";
}

function get_sem($id) {
    return "SELECT * from sem WHERE id='$id'";
}

?>
